package com.joinlink.omiis.commons.persistence.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

public abstract class AbstractDBProcessor<T> {

	/**
	 * 
	 * @return
	 * @throws Exception
	 */
	protected Connection getConnection() throws Exception {
		return DBCPPool.getInstance().createConnection();
	}

	/**
	 * query
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 * @throws Exception
	 */
	public List<Map<String, Object>> execQuery(String sql, Object... params) throws SQLException, Exception {
		Connection conn = getConnection();
		List<Map<String, Object>> resultList = new ArrayList<>();
		try {
			resultList = getQueryRunner().query(conn, sql, new MapListHandler(), params);
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			close(conn);
		}
		return resultList;
	}

	protected void close(Connection conn) {
		DbUtils.closeQuietly(conn);
	}

	protected void close(Statement statement) {
		DbUtils.closeQuietly(statement);
	}

	public List<T> execQuery2(String sql, Class<T> classType, Object... params) throws SQLException, Exception {
		Connection conn = getConnection();
		List<T> resultList = new ArrayList<>();
		try {

			resultList = (List<T>) getQueryRunner().query(conn, sql, new BeanListHandler(classType), params);
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			close(conn);
		}
		return resultList;
	}

	/**
	 * update、delete、insert
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 * @throws Exception
	 */
	public int execUpdate(String sql, Object... params) throws SQLException, Exception {
		Connection conn = getConnection();
		int counter = -1;
		try {
			counter = getQueryRunner().update(conn, sql, params);
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			close(conn);
		}
		return counter;
	}

	public int execInsert(String sql, T bean, String... columns) throws SQLException, Exception {
		Connection conn = getConnection();
		PreparedStatement ps = null;
		int counter = -1;
		try {
			ps = conn.prepareStatement(sql);
			getQueryRunner().fillStatementWithBean(ps, bean, columns);
			counter = ps.executeUpdate();
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			close(ps);
			close(conn);

		}
		return counter;
	}

	/**
	 * batch
	 * 
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 * @throws Exception
	 */
	public int execBatach(String sql, Object[][] params) throws SQLException, Exception {
		Connection conn = getConnection();
		int counter = -1;
		try {
			counter = getQueryRunner().batch(conn, sql, params).length;
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			close(conn);
		}
		return counter;
	}

	private QueryRunner getQueryRunner() {
		return new QueryRunner();
	}

}
